package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Properties;
import java.util.logging.Level;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class Mhrmtax extends X_hrm_tax {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 868046968655237699L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmtax.class);

	public Mhrmtax(Properties ctx, int hrm_tax_ID, String trxName) {
		super(ctx, hrm_tax_ID, trxName);
		
	}

	public Mhrmtax(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
		
	}

	public static Mhrmtax getTax(int C_BPartner_ID, Timestamp dt, String type) {
		
		if (C_BPartner_ID == 0)
			return null;

		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmtax hrmtax = new Mhrmtax(Env.getCtx(),0,null);
			
		// 
		String sql_ = "SELECT * FROM HRM_TAX \n " +
						"WHERE ISACTIVE='Y' \n " +
							" AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							" AND HRM_TYPETAX ='" + type + "' \n " +
							" AND DATEFROM=" + DB.TO_DATE(dt, true);
			try
			{
				pstmt = DB.prepareStatement(sql_,null);					
				rs = pstmt.executeQuery();	
				if(rs.next())
					hrmtax = new Mhrmtax(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return hrmtax;
		
	}
	
	/** Getting profit in month */
	public static BigDecimal getTotal(int C_BPartner_ID, Timestamp dt){
		
		if (C_BPartner_ID == 0)
			return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// select total in hrm_tax
		String sql_ = "SELECT ROUND(SUM(HRM_TOTAL)) FROM HRM_TAX \n " +
						"WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						" AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
						" AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt);
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
	/** Getting profit in month */
	public static BigDecimal getIPNofYear(int C_BPartner_ID, int rate, Timestamp dt){
		
		if (C_BPartner_ID == 0)
			return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// select total in hrm_tax
		String sql_ = "WITH TEMP AS ( \n SELECT TT.AD_CLIENT_ID, TT.AD_ORG_ID, TT.C_BPARTNER_ID, TT.HRM_IPN, " +
				      " TT.DATEFROM, EE.ISACTIVE, EE.STARTDATE, EE.ENDDATE, EE.RATE, EE.HRM_TAXTYPE	FROM HRM_TAX TT \n " +
				      " LEFT JOIN HRM_TAXEXEMPTIONSEMPLOYEE EE ON TT.C_BPARTNER_ID=EE.C_BPARTNER_ID	\n " +
				      " WHERE TT.DATEFROM BETWEEN EE.STARTDATE AND EE.ENDDATE \n ) \n " +
				      " SELECT ROUND(SUM(HRM_IPN)) FROM TEMP \n " +
						" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						" AND RATE=" + rate + " \n " +
						" AND TO_DATE('01-01-'||TO_CHAR(DATEFROM,'YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
						" AND TO_DATE('31-12-'||TO_CHAR(DATEFROM,'YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt);
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return (result == null) ? new BigDecimal(0) : result;
	}
	
public static BigDecimal getRelief(int C_BPartner_ID, Timestamp dt){
		
		if (C_BPartner_ID == 0)
			return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// select total in hrm_tax
		String sql_ = "SELECT coalesce(ROUND(sum(abs(hrm_relief))),0) FROM HRM_TAX \n " +
						" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						" AND " + DB.TO_DATE(dt) + " BETWEEN date_trunc('MONTH'::text, DATEFROM) " +
						" AND (date_trunc('MONTH'::text, DATEFROM) + '1 mon'::interval - '1 day'::interval)";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}

public static boolean isRelief(int C_BPartner_ID, Timestamp dt){
	
	if (C_BPartner_ID == 0)
		return false;	
	
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// select total in hrm_tax
	String sql_ = "SELECT COUNT(*) FROM HRM_TAX \n " +
					" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					" AND " + DB.TO_DATE(dt) + " BETWEEN date_trunc('MONTH'::text, DATEFROM) " +
					" AND (date_trunc('MONTH'::text, DATEFROM) + '1 mon'::interval - '1 day'::interval) " +
					" AND isRelief='Y'";
	try
	{
		pstmt = DB.prepareStatement(sql_,null);
		rs = pstmt.executeQuery();
		if(rs.next())
			return (rs.getInt(1) > 0) ? true : false;	
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}

	return false;
}

public static Mhrmtax getTaxType(int C_BPartner_ID, Timestamp dt, String type) {
	
	if (C_BPartner_ID == 0)
		return null;

	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmtax result = new Mhrmtax(Env.getCtx(),0,null);
		
	// 
	String sql_ = "SELECT * FROM HRM_TAX \n " +
					"WHERE ISACTIVE='Y' \n " +
						" AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						" AND HRM_TYPETAX ='" + type + "' \n " +
						" AND DATEFROM =" + DB.TO_DATE(dt, true);
		try
		{
			pstmt = DB.prepareStatement(sql_,null);				
			rs = pstmt.executeQuery();	
			if(rs.next())
				result = new Mhrmtax(Env.getCtx(), rs, null);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
	return result;	
	}

	public static BigDecimal getTotalOPV(int C_BPartner_ID, Timestamp dt){
	
	if (C_BPartner_ID == 0)
		return null;
			
	BigDecimal result = new BigDecimal(0);
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// select total in hrm_tax
	String sql_ = "SELECT CASE WHEN COALESCE(ROUND(SUM(HRM_OPV),2),0)> 139950.00 THEN 139950.00 ELSE COALESCE(ROUND(SUM(HRM_OPV),2),0) END  FROM HRM_TAX \n " +
					"WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					" AND " + DB.TO_DATE(dt) + " BETWEEN date_trunc('MONTH'::text, DATEFROM) " +
					" AND (date_trunc('MONTH'::text, DATEFROM) + '1 mon'::interval - '1 day'::interval) ";
	try
	{
		pstmt = DB.prepareStatement(sql_,null);
		rs = pstmt.executeQuery();
		if(rs.next())
			return rs.getBigDecimal(1);		
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}

	return result;
	}

}
